package edu.nus.comp.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import edu.nus.comp.dto.CacheBufferDTO;
import edu.nus.comp.dto.CommonDTO;
import edu.nus.comp.dto.RedoLogBufferDTO;
import edu.nus.comp.dto.SharedPoolDTO;
import edu.nus.comp.dto.SortingAreaDTO;

@Repository
public class BaseDAO {

    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int getNumberOfUsers(){
    	return this.jdbcTemplate.queryForInt("select count(*) from all_users");
    }
    
    public SharedPoolDTO getSharedPoolInfo (){
    	return this.jdbcTemplate.queryForObject(
    	        "select round ((info.bytes - stat.bytes)*100/info.bytes, 2) ratio  from v$sgainfo info, v$sgastat stat  where info.name = 'Shared Pool Size' and stat.pool = 'shared pool' and lower(stat.name) = 'free memory'",
    	        new RowMapper<SharedPoolDTO>() {
    	            public SharedPoolDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
    	            	SharedPoolDTO dto = new SharedPoolDTO();
    	            	dto.setRatio(Double.parseDouble(rs.getString("ratio")));
    	                return dto;
    	            }
    	        });
    }
    
    public CacheBufferDTO getCacheBufferInfo(){
    	return this.jdbcTemplate.queryForObject(
    	        "SELECT ROUND((1-(phy.VALUE / (cur.VALUE + con.VALUE)))*100,2) as ratio FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name = 'db block gets' AND con.name = 'consistent gets' AND phy.name = 'physical reads'",
    	        new RowMapper<CacheBufferDTO>() {
    	            public CacheBufferDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
    	            	CacheBufferDTO dto = new CacheBufferDTO();
    	             	dto.setRatio(Double.parseDouble(rs.getString("ratio")));
    	                return dto;
    	            }
    	        });
    }

    public RedoLogBufferDTO getRedoLogBufferInfo(){
    	return this.jdbcTemplate.queryForObject(
    	        "select round (a.value/b.value * 100, 2) ratio from v$sysstat a, v$sysstat b where a.name = 'redo buffer allocation retries' and b.name = 'redo entries'",
    	        new RowMapper<RedoLogBufferDTO>() {
    	            public RedoLogBufferDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
    	            	RedoLogBufferDTO dto = new RedoLogBufferDTO();
    	            	dto.setRatio(Double.parseDouble(rs.getString("ratio")));
    	                return dto;
    	            }
    	        });
    }
    public SortingAreaDTO getSortingAreaInfo(){
    	return this.jdbcTemplate.queryForObject(
    	        "SELECT round (mem.value*100/(disk.value + mem.value), 2) ratio FROM v$sysstat disk, v$sysstat mem WHERE mem.name = 'sorts (memory)' AND disk.name = 'sorts (disk)'",
    	        new RowMapper<SortingAreaDTO>() {
    	            public SortingAreaDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
    	            	SortingAreaDTO dto = new SortingAreaDTO();
    	            	dto.setRatio(Double.parseDouble(rs.getString("ratio")));
    	                return dto;
    	            }
    	        });
    }
}